﻿
/*
 * Create table UDS_Mail
 */
IF ( OBJECT_ID(N'dbo.UDS_Mail', N'U') IS NULL )
BEGIN
    CREATE TABLE dbo.UDS_Mail
    (
        MailId          int             NOT NULL IDENTITY(1,1),
        MailBodyId      int             NOT NULL,
        UserName        nvarchar(50)    COLLATE Chinese_PRC_CI_AS NOT NULL,
        FolderType      int             NOT NULL,
        ReadFlag        int             NOT NULL  CONSTRAINT [DF_UDS_Mail_ReadFlag] DEFAULT(0), -- 0: new mail
        TimeCreated     datetime        NOT NULL  CONSTRAINT [DF_UDS_Mail_TimeCreated] DEFAULT(getdate()),
        LastModified    datetime        NOT NULL  CONSTRAINT [DF_UDS_Mail_LastModified] DEFAULT(getdate()),

        CONSTRAINT [PK_UDS_Mail] PRIMARY KEY NONCLUSTERED ( MailId asc ),
        CONSTRAINT [FK_UDS_Mail_MailId] FOREIGN KEY (MailBodyId) REFERENCES dbo.UDS_MailBody([MailBodyID])
    );

    CREATE CLUSTERED INDEX [IX_UDS_Mail] ON dbo.UDS_Mail
    (
        UserName asc,
        FolderType asc,
        ReadFlag asc
    );
END
GO
